package com.clqteam.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.clqteam.dao.SelectVIPLXGDao;
import com.clqteam.model.Supplier;
import com.clqteam.model.VIP;
import com.clqteam.util.JDBCutil;
/**
 * 通过关键字查询vip信息
 * @author Administrator
 *LXG
 */
public class SelectVIPLXGDaoImpl implements SelectVIPLXGDao{
	/**
	 * 通过关键字查询vip信息
	 * @param key字符串关键字
	 * @param key1 int型关键字
	 * @param page 页面
	 * @param rows 页面大小
	 * @return
	 */
	@Override
	public List<VIP> selectVIP(String key, int key1,int page,int rows) {
		List <VIP>li=new ArrayList();
		StringBuffer sql=new StringBuffer("");
		sql.append(" SELECT s_vip.vId,s_vip.vPoint,s_vip.pitRate,s_vip.vDiscount,s_vip.vTime");
		sql.append(" FROM s_vip");
		sql.append(" WHERE s_vip.vId=?");
		sql.append(" OR s_vip.vTime LIKE ?");
		sql.append(" LIMIT ?,?");
		Connection con=JDBCutil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());			
			ps.setInt(1, key1);
			ps.setString(2, "%"+key+"%");
			ps.setInt(3, (page-1)*rows);
			ps.setInt(4, rows);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				VIP vip=new VIP();
				vip.setvId(rs.getInt("vId"));
				vip.setvPoint(rs.getInt("vPoint"));
				vip.setPitRate(rs.getDouble("pitRate"));
				vip.setvDiscount(rs.getDouble("vDiscount"));
				vip.setvTme(rs.getString("vTime"));
				li.add(vip);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return li;
	}
	/**
	 * 
	 *按条件查询的信息总数
	 * @param key
	 * @param key1
	 * @return
	 */
	@Override
	public int countVIp(String key, int key1) {
		int count=0;
		StringBuffer sql=new StringBuffer("");
		sql.append(" SELECT COUNT(s_vip.vId)AS tota");
		sql.append(" FROM s_vip");
		sql.append(" WHERE s_vip.vId=?");
		sql.append(" OR s_vip.vTime LIKE ?");
		
		Connection con=JDBCutil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());			
			ps.setInt(1, key1);
			ps.setString(2, "%"+key+"%");
		
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				count=rs.getInt("tota");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

}
